﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.ServiceModel.DomainServices.EntityFramework;
using System.ServiceModel.DomainServices.Server;
using System.Transactions;
using System.Web;
using System.Web.Security;
using BarcodeLib.Symbologies;
using GSSoftSL.Web.Models;
using GSSoftSL.Web.Models.Shared;
using Newtonsoft.Json;

namespace GSSoftSL.Web.Services
{
    public partial class GSSoftService
    {
        #region Phân quyền Main view

        [Invoke]
        public string[] GetDanhSachQuyen()
        {
            return Roles.GetRolesForUser();
        }

        #endregion

        #region Trường

        public void InsertTruong(Truong truong)
        {
            truong.MaTruong = (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaTruong AS INT)), 0) FROM Truong").
                                   Single() + 1).ToString().PadLeft(4, '0');
            if ((truong.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(truong, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Truongs.AddObject(truong);
            }
        }

        #endregion

        #region Ca học

        public void InsertCaHoc(CaHoc caHoc)
        {
            caHoc.MaCaHoc = (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaCaHoc AS INT)), 0) FROM CaHoc").
                                   Single() + 1).ToString().PadLeft(4, '0');
            if ((caHoc.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(caHoc, EntityState.Added);
            }
            else
            {
                this.ObjectContext.CaHocs.AddObject(caHoc);
            }
        }

        #endregion

        public IQueryable<GiaoVien> GetGiaoViensWithDetails()
        {
            return this.ObjectContext.GiaoViens.Include("GiaoVienMons");
        }

        public IQueryable<Mon> GetMonsWithGiaoVienMons()
        {
            //return this.ObjectContext.ExecuteStoreQuery<Mon>(@"SELECT m.* FROM Mon m INNER JOIN GiaoVienMon gvm ON m.MaMon = gvm.MaMon");
            //return this.ObjectContext.Mons.Include("GiaoVienMons").Include("GiaoVienMons.GiaoVien").Where(n => n.GiaoVienMons.Count > 0);
            return this.ObjectContext.Mons.Include("GiaoVienMons").Where(n => n.GiaoVienMons.Count > 0);
                //.Include("GiaoVienMons.GiaoVien");
        }

        public IQueryable<Lop> GetLopsWithGiaoViens_Mons()
        {
            return
                this.ObjectContext.Lops.Include("GiaoVien").Include("Mon").Include("LopChiTiets").Include(
                    "LopChiTiets.Phong");
        }

        /*public IQueryable<Lop> GetLopsWithGiaoVien()
        {
            return this.ObjectContext.Lops.Include("GiaoVien");
        }*/

        /*public int GetMaxMaGV()
        {
            return
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaGV AS INT)), 0) FROM GiaoVien").
                    Single();
        }*/

        

        public void UpdateGiaoVien(GiaoVien currentGiaoVien)
        {
            //this.ObjectContext.GiaoViens.AttachAsModified(currentGiaoVien, this.ChangeSet.GetOriginal(currentGiaoVien));

            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.GiaoViens.AddObject(currentGiaoVien);

            // Get the root Entity into the correct state.
            GiaoVien original = this.ChangeSet.GetOriginal(currentGiaoVien);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentGiaoVien,
                                                                        EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.GiaoViens.AttachAsModified(currentGiaoVien, original);
            }

            // Loop through all children and get them into the correct state.
            /*foreach (GiaoVienMon c in this.ChangeSet.GetAssociatedChanges(currentGiaoVien, p => p.GiaoVienMons))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.GiaoVienMons.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.GiaoVienMons.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }*/
        }

        public void InsertGiaoVien(GiaoVien giaoVien)
        {
            giaoVien.MaGV = (this.ObjectContext.ExecuteStoreQuery<int>(
                "SELECT ISNULL(MAX(CAST(MaGV AS INT)), 0) FROM GiaoVien").Single() + 1).ToString().PadLeft(4, '0');
            if ((giaoVien.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(giaoVien, EntityState.Added);
            }
            else
            {
                this.ObjectContext.GiaoViens.AddObject(giaoVien);
            }
        }


        #region Mon

        public void InsertMon(Mon mon)
        {
            mon.MaMon =
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaMon AS INT)), 0) FROM Mon").
                     Single() + 1).ToString().PadLeft(3, '0');
            if ((mon.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(mon, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Mons.AddObject(mon);
            }
        }

        #endregion

        #region Lop

        public void UpdateLop(Lop currentLop)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.Lops.AddObject(currentLop);

            // Get the root Entity into the correct state.
            Lop original = this.ChangeSet.GetOriginal(currentLop);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentLop, EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.Lops.AttachAsModified(currentLop, original);
            }

            // Loop through all children and get them into the correct state.
            foreach (KhoaHoc c in this.ChangeSet.GetAssociatedChanges(currentLop, p => p.KhoaHocs))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocs.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocs.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }
        }

        public void InsertLop(Lop lop)
        {
            lop.MaLop =
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaLop AS INT)), 0) FROM Lop").Single() +
                 1).ToString().PadLeft(4, '0');
            if ((lop.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(lop, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Lops.AddObject(lop);
            }
        }

        /*public int GetMaxMaLop()
        {
            return
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaLop AS INT)), 0) FROM Lop").Single();
        }*/

        public IQueryable<Lop> GetLopsWithKhoaHocs()
        {
            return this.ObjectContext.Lops.Include("KhoaHocs");//.Include("GiaoVien");
        }

        #endregion

        #region KhoaHoc

        public void InsertKhoaHoc(KhoaHoc khoaHoc)
        {
            int stt =
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM KhoaHoc WHERE LopId = {0}",
                                                          khoaHoc.LopId).Single() + 1;
            khoaHoc.STT = stt;
            khoaHoc.MaKhoaHoc = ObjectContext.Lops.Single(n => n.LopId == khoaHoc.LopId).MaLop + stt.ToString().PadLeft(4, '0');
            if ((khoaHoc.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(khoaHoc, EntityState.Added);
            }
            else
            {
                this.ObjectContext.KhoaHocs.AddObject(khoaHoc);
            }
        }

        public void UpdateKhoaHoc(KhoaHoc currentKhoaHoc)
        {
            //this.ObjectContext.GiaoViens.AttachAsModified(currentGiaoVien, this.ChangeSet.GetOriginal(currentGiaoVien));

            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.KhoaHocs.AddObject(currentKhoaHoc);

            // Get the root Entity into the correct state.
            KhoaHoc original = this.ChangeSet.GetOriginal(currentKhoaHoc);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentKhoaHoc,
                                                                        EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.KhoaHocs.AttachAsModified(currentKhoaHoc, original);
            }

            // Loop through all children and get them into the correct state.
            /*foreach (KhoaHocChiTiet c in this.ChangeSet.GetAssociatedChanges(currentKhoaHoc, p => p.KhoaHocChiTiets))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocChiTiets.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocChiTiets.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }*/

            // Loop through all children and get them into the correct state.
            /*foreach (KhoaHocCotDiem c in this.ChangeSet.GetAssociatedChanges(currentKhoaHoc, p => p.KhoaHocCotDiems))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocCotDiems.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocCotDiems.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }*/
            /*foreach (KhoaHocDiem c in this.ChangeSet.GetAssociatedChanges(currentKhoaHoc, p => p.KhoaHocDiems))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocDiems.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocDiems.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }*/
            /*foreach (KhoaHocHocVien c in this.ChangeSet.GetAssociatedChanges(currentKhoaHoc, p => p.KhoaHocHocViens))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.KhoaHocHocViens.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.KhoaHocHocViens.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }*/
        }

        public IQueryable<KhoaHoc> GetKhoaHocsWithLop()
        {
            return this.ObjectContext.KhoaHocs.Include("Lop");
        }

        public IQueryable<KhoaHoc> GetKhoaHocsWithChiTiet()
        {
            return this.ObjectContext.KhoaHocs.Include("KhoaHocChiTiets").Include("GiaoVien").Include("Lop");
        }

        public IQueryable<KhoaHoc> GetKhoaHocsWithGiaoVien()
        {
            return this.ObjectContext.KhoaHocs.Include("GiaoVien");
        }

        #endregion

        #region HocVien

        public void InsertHocVien(HocVien hocVien)
        {
            hocVien.STT = (this.ObjectContext.ExecuteStoreQuery<long>("SELECT ISNULL(MAX(STT), 0) FROM HocVien").Single() + 1);
            EAN13 ean = new EAN13("100" + hocVien.STT.ToString().PadLeft(9, '0'));
            hocVien.MaHV = ean.RawData;
            if ((hocVien.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(hocVien, EntityState.Added);
            }
            else
            {
                this.ObjectContext.HocViens.AddObject(hocVien);
            }
        }

        public IQueryable<HocVien> GetHocVienFilters(string MaHV, string HoLot, string Ten, int? GioiTinh, string Email, string DienThoai)
        {
            var query = this.ObjectContext.HocViens as IQueryable<HocVien>;//.Where(n => n.MaHV.Contains("123")).Where(n => n.Ten.Contains("123"));
            if (MaHV != "")
                query = query.Where(n => n.MaHV.Contains(MaHV));
            if (HoLot != "")
                query = query.Where(n => n.HoLot.Contains(HoLot));
            if (Ten != "")
                query = query.Where(n => n.Ten.Contains(Ten));
            if (Email != "")
                query = query.Where(n => n.Email.Contains(Email));
            if (DienThoai != "")
                query = query.Where(n => n.DTCaNhan.Contains(DienThoai));
            if (GioiTinh.HasValue)
                query = query.Where(n => n.GioiTinh == GioiTinh);
            return query;
        }

        /*public IQueryable<Lop> GetLopsHocVienTheoHoc(string MaHV)
        {
            var query = this.ObjectContext.KhoaHocHocViens
                .Include("KhoaHoc")
                .Include("KhoaHoc.Lop")
                .Include("KhoaHoc.GiaoVien")
                .Where(n => n.MaHV == MaHV)
                .Select(n => n.KhoaHoc.Lop);
            return query;
        }*/

        public IQueryable<KhoaHoc> GetKhoaHocsHocVienTheoHoc(string MaHV)
        {
            var query = this.ObjectContext.KhoaHocs.Include("GiaoVien")
                .Where(n => n.KhoaHocHocViens.Any(m => m.MaHV == MaHV && m.TrangThai == 0)).Distinct();
            return query;
        }

        public void RegisterLopsHocVien(string MaHV, Guid[] Lops)
        {
            /*var lst = this.ObjectContext.LopHocViens.Where(n => n.MaHV == MaHV).ToArray();
            var lstUpdateIdKhongTheo = lst.Select(n => n.LopId).Except(Lops);
            var lstUpdateKhongTheo = from LopHocVien lhv in lst
                                     join Guid g in lstUpdateIdKhongTheo on lhv.LopId equals g
                                     select lhv;
            foreach (LopHocVien lhv in lstUpdateKhongTheo)
                lhv.ConTheoHoc = false;

            var lstUpdateIdTheo = lst.Select(n => n.LopId).Intersect(Lops);
            var lstUpdateTheo = from LopHocVien lhv in lst
                                join Guid g in lstUpdateIdTheo on lhv.LopId equals g
                                select lhv;
            foreach (LopHocVien lhv in lstUpdateTheo)
                lhv.ConTheoHoc = true;

            var lstAddId = Lops.Except(lst.Select(n => n.LopId));
            foreach (Guid lop in lstAddId)
            {
                this.ObjectContext.LopHocViens.AddObject(new LopHocVien()
                                                             {
                                                                 MaHV = MaHV,
                                                                 LopId = lop,
                                                                 ConTheoHoc = true
                                                             });
            }
            this.ObjectContext.SaveChanges();*/
        }

        public void RegisterKhoaHocsHocVien(string MaHV, Guid[] KhoaHocs)
        {
            var lst = this.ObjectContext.KhoaHocHocViens.Where(n => n.MaHV == MaHV).ToArray();
            var lstUpdateIdKhongTheo = lst.Select(n => n.KhoaHocId).Except(KhoaHocs);
            var lstUpdateKhongTheo = from KhoaHocHocVien khhv in lst
                                     join Guid g in lstUpdateIdKhongTheo on khhv.KhoaHocId equals g
                                     select khhv;
            foreach (KhoaHocHocVien khhv in lstUpdateKhongTheo)
                khhv.TrangThai = 1;

            var lstUpdateIdTheo = lst.Select(n => n.KhoaHocId).Intersect(KhoaHocs);
            var lstUpdateTheo = from KhoaHocHocVien khhv in lst
                                join Guid g in lstUpdateIdTheo on khhv.KhoaHocId equals g
                                select khhv;
            foreach (KhoaHocHocVien khhv in lstUpdateTheo)
                khhv.TrangThai = 0;

            var lstAddId = KhoaHocs.Except(lst.Select(n => n.KhoaHocId));
            foreach (Guid lop in lstAddId)
            {
                this.ObjectContext.KhoaHocHocViens.AddObject(new KhoaHocHocVien()
                {
                    MaHV = MaHV,
                    KhoaHocId = lop,
                    TrangThai = 0
                });
            }
            this.ObjectContext.SaveChanges();
        }

        /*public KhoaHocHocPhi[] GetKhoaHocsDaDongHocPhi(string MaHV)
        {
            string sql =
                @"
SELECT	kh.KhoaHocId, kh.LopId, kh.MaKhoaHoc, kh.TenKhoaHoc, kh.BatDau, kh.KetThuc, 
        l.TenLop, m.TenMon, gv.TenGV, kh.HocPhi AS HocPhiDeNghi, CAST(0 AS numeric(18,2)) AS MucGiam, kh.HocPhi AS HocPhiSauGiam,
        CAST(CASE 
                WHEN khct.KhoaHocId IS NULL THEN 0
                ELSE 1
                END AS BIT) DaDong
FROM (SELECT * FROM KhoaHocHocVien WHERE MaHV = {0}) khhv
INNER JOIN KhoaHoc kh  ON kh.KhoaHocId = khhv.KhoaHocId
INNER JOIN Lop l ON l.LopId = kh.LopId
INNER JOIN Mon m ON m.MaMon = l.MaMon
INNER JOIN GiaoVien gv ON gv.GiaoVienID = kh.GiaoVienId
LEFT JOIN
(
	SELECT DISTINCT blc.KhoaHocId 
    FROM BienLai bl INNER JOIN BienLaiCT blc ON bl.BienLaiId = blc.BienLaiId
    WHERE bl.MaHV = {0}
) AS khct ON kh.KhoaHocId = khct.KhoaHocId
ORDER BY m.MaMon";
            KhoaHocHocPhi[] res = this.ObjectContext.ExecuteStoreQuery<KhoaHocHocPhi>(sql, MaHV).ToArray();
            return res;
        }*/

        public KhoaHocHocPhi[] GetKhoaHocsDaDongHocPhi(string MaHV, Guid LopId)
        {
            string sql =
                @"
SELECT	kh.KhoaHocId, kh.LopId, kh.MaKhoaHoc, kh.TenKhoaHoc, kh.BatDau, kh.KetThuc, 
        l.TenLop, m.TenMon, gv.TenGV, kh.HocPhi AS HocPhiDeNghi, CAST(0 AS numeric(18,2)) AS MucGiam, 
        kh.HocPhi AS HocPhiSauGiam, ISNULL(khhv.TrangThaiHocPhi, 0) TrangThaiHocPhi,
        CAST(CASE 
				WHEN khhv.TrangThaiHocPhi IS NULL OR khhv.TrangThaiHocPhi = 0 THEN 1
				ELSE 0
			 END AS BIT) CanChangeTrangThaiHocPhi
FROM KhoaHoc kh
INNER JOIN Lop l ON l.LopId = kh.LopId
INNER JOIN Mon m ON m.MaMon = l.MaMon
INNER JOIN GiaoVien gv ON gv.GiaoVienID = kh.GiaoVienId
LEFT JOIN (SELECT * FROM KhoaHocHocVien WHERE MaHV = {0}) khhv 
	ON kh.KhoaHocId = khhv.KhoaHocId
WHERE l.LopId = {1}
ORDER BY m.MaMon";
            KhoaHocHocPhi[] res = this.ObjectContext.ExecuteStoreQuery<KhoaHocHocPhi>(sql, MaHV, LopId).ToArray();
            return res;
        }

        /*public void DongHocPhi(string MaHV, )
        {
            
        }*/

        #endregion

        #region Phong

        public void InsertPhong(Phong phong)
        {
            phong.MaPhong = (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(MaPhong AS INT)), 0) FROM Phong").
                                   Single() + 1).ToString().PadLeft(4, '0');
            if ((phong.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(phong, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Phongs.AddObject(phong);
            }
        }

        #endregion

        #region BienLai

        public void InsertBienLai(BienLai bienLai)
        {
            int max =
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(CAST(SUBSTRING(SoBienLai, 3, 6) AS INT)), 0) FROM BienLai").
                    Single();
            string soBienLai = DateTime.Now.Year.ToString().Substring(2, 2) + (max + 1).ToString().PadLeft(6, '0');
            bienLai.SoBienLai = soBienLai;
            bienLai.NgayTao = DateTime.Now;
            if ((bienLai.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(bienLai, EntityState.Added);
            }
            else
            {
                this.ObjectContext.BienLais.AddObject(bienLai);
            }
        }

        public void UpdateBienLai(BienLai currentBienLai)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            this.ObjectContext.BienLais.AddObject(currentBienLai);

            // Get the root Entity into the correct state.
            BienLai original = this.ChangeSet.GetOriginal(currentBienLai);

            if (original == null)
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(currentBienLai, EntityState.Unchanged);
            }
            else
            {
                this.ObjectContext.BienLais.AttachAsModified(currentBienLai, original);
            }

            // Loop through all children and get them into the correct state.
            foreach (BienLaiCT c in this.ChangeSet.GetAssociatedChanges(currentBienLai, p => p.BienLaiCTs))
            {
                ChangeOperation change = this.ChangeSet.GetChangeOperation(c);
                switch (change)
                {
                    case ChangeOperation.Delete:
                        if (c.EntityState == EntityState.Detached)
                        {
                            this.ObjectContext.BienLaiCTs.Attach(c);
                        }
                        this.ObjectContext.DeleteObject(c);
                        break;
                    case ChangeOperation.Insert:
                        // This is already done for us.
                        break;
                    case ChangeOperation.None:
                        this.ObjectContext.ObjectStateManager.ChangeObjectState(c, EntityState.Unchanged);
                        break;
                    case ChangeOperation.Update:
                        this.ObjectContext.BienLaiCTs.AttachAsModified(c, this.ChangeSet.GetOriginal(c));
                        break;
                    default:
                        break;
                }
            }
        }

        #endregion

        #region Điểm danh

        /*
        SELECT	hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, ISNULL(ddc.CoPhep, 0) CoPhep, ddc.LyDo,
		CASE ddc.MaHV
			WHEN NULL THEN 0
			ELSE 1
		END Vang
FROM HocVien hv 
LEFT JOIN (SELECT * FROM LopHocVien WHERE LopId = '')  lhv ON hv.MaHV = lhv.MaHV
LEFT JOIN (SELECT * FROM DiemDanhCT WHERE DiemDanhId = '') ddc ON hv.MaHV = ddc.MaHV
        */

        public DiemDanhCTInfo[] GetDiemDanhCTInfo(Guid KhoaHocId, Guid DiemDanhId)
        {
            return
                this.ObjectContext.ExecuteStoreQuery<DiemDanhCTInfo>(
                    @"
SELECT hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, t.Vang, t.CoPhep, t.LyDo
FROM HocVien hv INNER JOIN
(
	SELECT	ISNULL(t1.MaHV, t2.MaHV) MaHV, 
			CAST(CASE 
				    WHEN t2.MaHV IS NULL THEN 0
				    ELSE 1  
			     END AS BIT) Vang,
			CAST(ISNULL(t2.CoPhep, 0) AS BIT) CoPhep, t2.LyDo
	FROM
	(SELECT MaHV FROM KhoaHocHocVien WHERE KhoaHocId = {0} AND TrangThai = 0) t1
	FULL OUTER JOIN
	(SELECT MaHV, CoPhep, LyDo FROM DiemDanhCT WHERE DiemDanhId = {1}) t2
	ON t1.MaHV = t2.MaHV
) t
ON hv.MaHV = t.MaHV", KhoaHocId, DiemDanhId)
                    .ToArray();
        }

        public DiemDanhCTInfo[] GetDiemDanhDSHV(Guid KhoaHocId)
        {
            var query = 
                this.ObjectContext.ExecuteStoreQuery<DiemDanhCTInfo>(
                    @"
SELECT	hv.MaHV, hv.NgaySinh, hv.HoLot + ' ' + hv.Ten HoTen, CAST(0 AS BIT) Vang, CAST(0 AS BIT) CoPhep, N'' LyDo
FROM HocVien hv 
INNER JOIN (SELECT * FROM KhoaHocHocVien WHERE KhoaHocId = {0}) khhv 
ON hv.MaHV = khhv.MaHV", KhoaHocId)
                    .ToArray();
            return query;
        }

        [Invoke]
        public void DiemDanhAddNew(DiemDanh master, List<DiemDanhCT> details)
        {
            this.ObjectContext.DiemDanhs.AddObject(master);
            foreach (DiemDanhCT diemDanhCt in details)
            {
                this.ObjectContext.DiemDanhCTs.AddObject(diemDanhCt);
            }
            this.ObjectContext.SaveChanges();
            //this.ObjectContext.DiemDanhCTs.AddObject();
        }

        [Invoke]
        public void DiemDanhUpdate(DiemDanh master, List<DiemDanhCT> details)
        {
            // Add the entire graph so duplicate keys on new Entities are allowed.
            //this.ObjectContext.DiemDanhs.AddObject(master);
            /*this.ObjectContext.DiemDanhs.AttachAsModified(master,
                                                          this.ObjectContext.DiemDanhs.Single(
                                                              n => n.DiemDanhId == master.DiemDanhId));*/
            var original = this.ObjectContext.DiemDanhs.Single(n => n.DiemDanhId == master.DiemDanhId);
            original.NguoiCapNhat = master.NguoiCapNhat;
            original.NgayCapNhat = master.NgayCapNhat;
            original.SoLuongHV = master.SoLuongHV;
            original.SoLuongVang = master.SoLuongVang;
            original.CoGiaoVien = master.CoGiaoVien;
            //this.ObjectContext.ObjectStateManager.ChangeObjectState(master, EntityState.Modified);
            // Get the root Entity into the correct state.
            //DiemDanh original = this.ChangeSet.GetOriginal(master);
            /*if (original == null)
                this.ObjectContext.ObjectStateManager.ChangeObjectState(master, EntityState.Unchanged);
            else
                this.ObjectContext.DiemDanhs.AttachAsModified(master, original);*/

            var delete = this.ObjectContext.DiemDanhCTs.Where(n => n.DiemDanhId == master.DiemDanhId);
            foreach (DiemDanhCT ct in delete)
                this.ObjectContext.DiemDanhCTs.DeleteObject(ct);

            foreach (DiemDanhCT ct in details)
                this.ObjectContext.DiemDanhCTs.AddObject(ct);
            this.ObjectContext.SaveChanges();
        }

        #endregion

        #region Điểm

        public void InsertKhoaHocCotDiem(KhoaHocCotDiem khoaHocCotDiem)
        {
            /*khoaHoc.MaKhoaHoc = khoaHoc.Lop.MaLop +
                (this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM KhoaHoc WHERE LopId = {0}", khoaHoc.LopId).
                     Single() + 1).ToString().PadLeft(4, '0');*/
            khoaHocCotDiem.STT =
                this.ObjectContext.ExecuteStoreQuery<int>("SELECT ISNULL(MAX(STT), 0) FROM KhoaHocCotDiem WHERE KhoaHocId = {0}",
                                                          khoaHocCotDiem.KhoaHocId).Single() + 1;

            if ((khoaHocCotDiem.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(khoaHocCotDiem, EntityState.Added);
            }
            else
            {
                this.ObjectContext.KhoaHocCotDiems.AddObject(khoaHocCotDiem);
            }
        }

        [Invoke]
        public CotDiemInfo[] GetCotDiemInfos(Guid KhoaHocId)
        {
            return this.ObjectContext.KhoaHocCotDiems.Where(n => n.KhoaHocId == KhoaHocId)
                .Select(n => new CotDiemInfo()
                                 {
                                     STT = n.STT,
                                     TenCot = n.Ten,
                                     IsSelected = false,
                                     KhoaHocId = n.KhoaHocId
                                 }).ToArray();
        }

        /*
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, Cot1, Cot2
FROM HocVien hv
INNER JOIN
(
	SELECT MaHV, [1] Cot1, [2] Cot2
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM LopDiem
		WHERE LopId = '0F157A45-A862-470A-9451-8CF25C06F87B'
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ([1], [2])
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
 */
        [Invoke]
        public string GetJsonDiemsByKhoaHoc(Guid KhoaHocId)
        {
            DiemSo diem = new DiemSo();
            diem.KhoaHocId = KhoaHocId;
            var CotDiems = this.ObjectContext.KhoaHocCotDiems.Where(n => n.KhoaHocId == KhoaHocId)
                .Select(n => new CotDiemInfo()
                                 {
                                     STT = n.STT,
                                     TenCot = n.Ten
                                 }).ToList();
            string strIn = string.Join(",", CotDiems.Select(n => "[" + n.STT + "]"));
            string strSelect1 = string.Join(",", CotDiems.Select(n => "Cot" + n.STT));
            string strSelect2 = string.Join(",", CotDiems.Select(n => "[" + n.STT + "] Cot" + n.STT ));
            string sql = string.Format(@"
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, {0}
FROM HocVien hv
INNER JOIN
(
	SELECT MaHV, {1}
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM KhoaHocDiem
		WHERE KhoaHocId = {{0}}
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ({2})
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
", strSelect1, strIn, strSelect2);
            diem.Diems = this.ObjectContext.ExecuteStoreQuery<HocVienDiem>(sql, KhoaHocId).ToList();
            return JsonConvert.SerializeObject(diem, Formatting.Indented,
                                               new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore});

            //return null;
        }

        [Invoke]
        public string GetJsonDiemsByKhoaHocCots(Guid KhoaHocId, int[] Cots)
        {
            DiemSo diem = new DiemSo();
            diem.KhoaHocId = KhoaHocId;
            List<string> strCots = Cots.Select(n => n.ToString()).ToList();
            string strIn = string.Join(",", strCots.Select(n => "[" + n + "]"));
            string strSelect = string.Join(",", strCots.Select(n => "[" + n + "] Cot" + n));
            string sql = string.Format(@"
SELECT hv.MaHV, hv.HoLot + ' ' + hv.Ten HoTen, {0}
FROM HocVien hv
INNER JOIN (SELECT * FROM KhoaHocHocVien WHERE KhoaHocId = {{0}}) khhv 
	ON hv.MaHV = khhv.MaHV
LEFT JOIN
(
	SELECT MaHV, {1}
	FROM 
	(
		SELECT MaHV, [Cot], Diem FROM KhoaHocDiem
		WHERE KhoaHocId = {{0}}
	) p
	PIVOT
	(
		SUM(Diem)
		FOR [Cot] IN ({2})
	) AS pvt
) d ON hv.MaHV = d.MaHV
ORDER BY hv.MaHV
", strSelect, strIn, strIn);
            //strCots.AddRange(new []{"MaHV", "HoTen"});
            List<string> CotContract = new List<string>();
            CotContract.AddRange(Cots.Select(n => "Cot" + n));
            CotContract.AddRange(new[] { "MaHV", "HoTen" });
            diem.Diems = this.ObjectContext.ExecuteStoreQuery<HocVienDiem>(sql, KhoaHocId).ToList();
            return JsonConvert.SerializeObject(diem, Formatting.Indented,
                                               new JsonSerializerSettings 
                                               {
                                                   ContractResolver = new DiemSoContractResolver(CotContract.ToArray())
                                               });

            //return null;
        }

        public void SaveDiemSo(string JsonStr)
        {
            DiemSo ds = JsonConvert.DeserializeObject<DiemSo>(JsonStr);
            List<KhoaHocDiem> lstOriginal = this.ObjectContext.KhoaHocDiems.Where(n => ds.Cots.Contains(n.Cot)).ToList();
            List<KhoaHocDiem> lstClient = new List<KhoaHocDiem>();
            //this.ObjectContext.LopDiems.in
            KhoaHocDiem tmp;
            PropertyInfo pi;
            decimal? diem;
            foreach (int cot in ds.Cots)
            {
                foreach (HocVienDiem item in ds.Diems)
                {
                    pi = typeof (HocVienDiem).GetProperty("Cot" + cot);
                    diem = (decimal?)pi.GetValue(item, null);
                    if (diem != null && diem.HasValue)
                    {
                        tmp = new KhoaHocDiem();
                        tmp.Cot = cot;
                        tmp.MaHV = item.MaHV;
                        tmp.Diem = diem.Value;
                        lstClient.Add(tmp);
                    }
                }
            }
            Guid NguoiTao = (Guid) Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey;
            DateTime now = DateTime.Now;
            var lstAdd = lstClient.Except(lstOriginal, new LambdaComparer<KhoaHocDiem>((x, y) => x.MaHV == y.MaHV && x.Cot == y.Cot));
            var lstDelete = lstOriginal.Except(lstClient, new LambdaComparer<KhoaHocDiem>((x, y) => x.MaHV == y.MaHV && x.Cot == y.Cot));
            var lstUpdate = from KhoaHocDiem ldOriginal in lstOriginal
                            join KhoaHocDiem ldClient in lstClient
                                on new {ldOriginal.MaHV, ldOriginal.Cot} equals new {ldClient.MaHV, ldClient.Cot}
                            where ldOriginal.Diem != ldClient.Diem
                            select new {Original = ldOriginal, Client = ldClient};
            foreach (KhoaHocDiem ld in lstAdd)
            {
                ld.KhoaHocId = ds.KhoaHocId;
                ld.NgayTao = now;
                ld.NguoiTao = NguoiTao;
                this.ObjectContext.KhoaHocDiems.AddObject(ld);
            }
            foreach (KhoaHocDiem ld in lstDelete)
                this.ObjectContext.KhoaHocDiems.DeleteObject(ld);
            foreach (var item in lstUpdate)
                item.Original.Diem = item.Client.Diem;
            this.ObjectContext.SaveChanges();
        }

        #endregion
    }
}